package cn.tedu;

import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo03 {
    @Test
    public void t1(){
        try (Connection conn = DBUtils.getConn();){
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("select job,count(*) from emp group by job order by count(*) desc limit 0,1");
            while (rs.next()){
                String job = rs.getString(1);
                int count = rs.getInt(2);
                System.out.println("job:"+job+",count:"+count);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    @Test
    public void t2(){
        try (Connection conn = DBUtils.getConn();){
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("select job,avg(sal) a from emp group by job order by a desc limit 0,3");
            while (rs.next()){
                String job = rs.getString(1);
                double avg_sal = rs.getDouble(2);
                System.out.println("job:"+job+"\t\tavg:"+avg_sal);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    @Test
    public void t3(){
        try (Connection conn = DBUtils.getConn();){
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("select job,count(*) c from emp group by job having c<3");            while (rs.next()){
                String job = rs.getString(1);
                int count = rs.getInt(2);
                System.out.println("job:"+job+"\t\tcount:"+count);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    @Test
    public void t4(){
        try (Connection conn = DBUtils.getConn();){
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("select count(*) from emp where deptId=(select deptId from emp where sal=(select max(sal) from emp))");            while (rs.next()){
                int count = rs.getInt(1);
                System.out.println("count:"+count);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    @Test
    public void t5(){
        try (Connection conn = DBUtils.getConn();){
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("select e.name,sal,d.name from emp e join dept d on e.deptId=d.id where sal>2000");
            while (rs.next()){
                String e_name = rs.getString("e.name");
                double sal = rs.getDouble("sal");
                String d_name = rs.getString("d.name");
                System.out.println("job:"+e_name+"\t\tsal:"+sal+"\t\td_name:"+d_name);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    @Test
    public void t6(){
        try (Connection conn = DBUtils.getConn();){
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("select d.name,e.name,sal from emp e right join dept d on e.deptId=d.id");            while (rs.next()){
                String d_name = rs.getString("d.name");
                String e_name = rs.getString("e.name");
                int sal = rs.getInt("sal");
                System.out.println("d.name:"+d_name+"\t\te.name:"+e_name+"\t\tsal:"+sal);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
